﻿using LinkSql;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using hospital_registration_system.Entity;

namespace hospital_registration_system.DAL
{
    public class PatientsDao
    {
        private string tableName = "patients";
        MySqlDataAdapter mySqlDataAdapter;
        /// <summary>
        /// 患者登录
        /// </summary>
        /// <param name="tel">电话</param>
        /// <param name="key">密码</param>
        /// <returns></returns>
        public Patients Login(string tel, string key)
        {
            Patients patients = null;
            MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            try
            {
                connection.Open();
                string sql = "select * from patients where telephone='" + tel + "'and patient_password='" + key + "'";
                mySqlDataAdapter = new MySqlDataAdapter(sql, connection);
                DataTable dataTable = new DataTable();
                mySqlDataAdapter.Fill(dataTable);
                if (dataTable.Rows.Count == 0)
                {
                    return null;
                }
                else
                {
                    patients = new Patients(
                    int.Parse(dataTable.Rows[0]["id"].ToString()),
                    dataTable.Rows[0]["nickname"].ToString(),
                    dataTable.Rows[0]["patient_name"].ToString(),
                    dataTable.Rows[0]["sex"].ToString(),
                    int.Parse(dataTable.Rows[0]["age"].ToString()),
                    dataTable.Rows[0]["telephone"].ToString(),
                    dataTable.Rows[0]["email"].ToString(),
                    dataTable.Rows[0]["address"].ToString(),
                    dataTable.Rows[0]["patient_password"].ToString(),
                    dataTable.Rows[0]["created_by"].ToString(),
                    (DateTime)(dataTable.Rows[0]["created_time"]),
                    dataTable.Rows[0]["updated_by"].ToString(),
                    (DateTime)(dataTable.Rows[0]["updated_time"]),
                    int.Parse(dataTable.Rows[0]["state"].ToString())
                                    );

                }
            }
            catch (MySqlException)
            {
                return null;
            }
            finally
            {

                connection.Close();
            }
            return patients;
        }

        /// <summary>
        /// 通过邮箱登录
        /// </summary>
        /// <param name="email"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public Patients LoginByEmail(string email,string pwd)
        {
            Dictionary<string, Object> map = new Dictionary<string, object>();
            map.Add("email", email);
            map.Add("patient_password", pwd);
            List<Patients> ret = GetPatientsByDataTable(MySQLUtils.selectByMap(tableName, map));
            if (ret.Count == 0)
                return null;
            return ret[0];

            //Patients patients = null;
            //MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            //try
            //{
            //    connection.Open();
            //    string sql = "select * from patients where email='" + email + "'and patient_password='" + pwd + "'";
            //    mySqlDataAdapter = new MySqlDataAdapter(sql, connection);
            //    DataTable dataTable = new DataTable();
            //    mySqlDataAdapter.Fill(dataTable);
            //    if (dataTable.Rows.Count == 0)
            //    {
            //        return null;
            //    }
            //    else
            //    {
            //        patients = GetPatientsByDataTable(dataTable)[0];

            //    }
            //}
            //catch (MySqlException)
            //{
            //    return null;
            //}
            //finally
            //{

            //    connection.Close();
            //}
            //return patients;

        }

        /// <summary>
        /// 通过对象增加
        /// </summary>
        /// <param name="patients"></param>
        /// <returns></returns>
        public bool insert(Patients patients)
        {
            MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            try
            {
                string sql = "insert into patients(nickname,patient_name,sex,age,telephone,email,address,patient_password,created_by,created_time,updated_by,updated_time,state) values("
                    + "'" + patients.Nickname + "'" + ","
                    + "'" + patients.Patient_name + "'" + ","
                    + "'" + patients.Sex + "'" + ","
                    + "'" + patients.Age + "'" + ","
                    + "'" + patients.Telephone + "'" + ","
                    + "'" + patients.Email + "'" + ","
                    + "'" + patients.Address + "'" + ","
                    + "'" + patients.Patient_password + "'" + ","
                    + "'" + patients.CREATED_BY1 + "'" + ","
                    + "'" + patients.CREATED_TIME1 + "'" + ","
                    + "'" + patients.UPDATED_BY1 + "'" + ","
                    + "'" + patients.UPDATED_TIME1 + "'" + ","
                    + "'" + patients.State + "'"
                    + ")";
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                cmd.ExecuteNonQuery();


            }
            catch (MySqlException)
            {

                return false;
            }
            finally
            {
                connection.Close();
            }

            return true;
        }

        /// <summary>
        /// 通过id删除信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool delete(int id)
        {
            MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            string sql = "delete from patients where id = '" + id + "'";
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException)
            {
                return false;
            }
            finally
            {
                connection.Close();
            }
            return true;
        }

        /// <summary>
        /// 通过对象修改信息
        /// </summary>
        /// <param name="patients"></param>
        /// <returns></returns>
        public bool update(Patients patients)
        {

            MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            string sql = "update patients set "
                + "nickname='" + patients.Nickname + "',"
                + "patient_name='" + patients.Patient_name + "'," +
                "sex='" + patients.Sex + "'," +
                "age='" + patients.Age + "'," +
                "telephone='" + patients.Telephone + "'," +
                "email='" + patients.Email + "'," +
                "address='" + patients.Address + "'," +
                "patient_password='" + patients.Patient_password + "'," +
                "created_by='" + patients.CREATED_BY1 + "'," +
                "created_time='" + patients.CREATED_TIME1 + "'," +
                "updated_by='" + patients.UPDATED_BY1 + "'," +
                "updated_time='" + patients.UPDATED_TIME1 + "'," +
                "state='" + patients.State + "' " +
                "where id = " + patients.Id
                ;
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException)
            {
                return false;
            }
            finally
            {
                connection.Close();
            }
            return true;

        }


        /// <summary>
        /// 通过id查询
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Patients selectById(int id)
        {
            return GetPatientsByDataTable(MySQLUtils.selectById(tableName, id))[0];
        }


        /// <summary>
        /// 查询所有
        /// </summary>
        /// <returns></returns>
        public List<Patients> selectAll()
        {
            return GetPatientsByDataTable(MySQLUtils.selectAll(tableName));
        }


        public List<Patients> GetPatientsByDataTable(DataTable dataTable)
        {
            List<Patients> patients = new List<Patients>();
            if (dataTable.Rows.Count == 0)
            {
                return patients;
            }
            foreach (DataRow row in dataTable.Rows)
            {
                
                Patients patient = new Patients(
                    int.Parse(row["id"].ToString()),
                    row["nickname"].ToString(),
                    row["patient_name"].ToString(),
                    row["sex"].ToString(),
                    int.Parse(row["age"].ToString()),
                    row["telephone"].ToString(),
                    row["email"].ToString(),
                    row["address"].ToString(),
                    row["patient_password"].ToString(),
                    row["created_by"].ToString(),
                    (DateTime)(row["created_time"]),
                    row["updated_by"].ToString(),
                    (DateTime)(row["updated_time"]),
                    int.Parse(row["state"].ToString())
                );
                patients.Add(patient);
            }
            return patients;
        }

    }
}
